/*
	This procedure loads all the shipped packing slips information.
	EXEC sp_revenue_shipment_report '0','0','0','0','0', '3' ,'0','XLREPORT','0'

*/
alter PROC sp_revenue_shipment_report

@CUSTOMER_ID 	VARCHAR(100),
@PRODUCT_ID	VARCHAR(200),
@SO_NUMBER	VARCHAR(50),
@FROMDATE	VARCHAR(50),
@TODATE		VARCHAR(50),
@CATEGORY	VARCHAR(10),
@INVOICE_NO	VARCHAR(50),
@REPORT_TYPE	VARCHAR(20),
@DEFAULT_PROMPT	VARCHAR(50)

AS
BEGIN
	CREATE TABLE #INVOICE_TABLE
		(	[LINE ITEM NUMBER] INT,[Qty Pulled] INT,[Qty shipped] int,[Price each] FLOAT,[Total Amount] FLOAT,[Ship Date] DateTime,
			[Item Description] varchar(200),[Customer Part Number] varchar(50),[Packing Slip #] varchar(50),[INVOICE NUMBER] VARCHAR(50),[Ship Via] varchar(50),
			[Bill To ADDRESS1] varchar(50),[Bill To ADDRESS2] varchar(50),[Bill To ADDRESS3] varchar(50),[Bill To ADDRESS4] varchar(50),[Bill To CITY] varchar(50),[Bill To STATE] varchar(50),
			[Bill To ZIPCODE] varchar(50),[Bill To COUNTRY] varchar(50),[Bill To PHONE NUMBER] varchar(50),
			[Bill to email] varchar(50),[Ship To ADDRESS1] varchar(50),[Ship To ADDRESS2] varchar(50),[Ship To ADDRESS3] varchar(50),[Ship To ADDRESS4] varchar(50),[Ship To CITY] varchar(50),[Ship To STATE] varchar(50),
			[Ship To ZIPCODE] varchar(50),[Ship To COUNTRY] varchar(50),[Ship To PHONE NUMBER] varchar(50),
			[Sales Order #] varchar(50),[PO Number] varchar(50),Term varchar(50),[Item Number] varchar(50),[Customer Name] varchar(65), LOCATION VARCHAR(50),
			[CREDIT NOTE #] VARCHAR(50), STATUS VARCHAR(50),REASON VARCHAR(100),[IS CREDIT/DEBIT] BIT,[CONFIRMED DATE] DATETIME, [ORDER TYPE] VARCHAR(50), [LATEST SCH DATE] DATETIME )

	DECLARE @SQLSTRING 		NVARCHAR(4000)
	DECLARE @CREADITSTRING		NVARCHAR(4000)
	DECLARE @PARAMDEFINITION 	NVARCHAR(700)
	DECLARE @START_DATE		DATETIME
	DECLARE	@END_DATE		DATETIME
	DECLARE @SALES_ORDER_STRING 	NVARCHAR(50)
	DECLARE @INVOICE_NO_STRING 	NVARCHAR(50)
	DECLARE @NewLine CHAR(2) 
	SET @NewLine=CHAR(13)+CHAR(10)

	SET @SALES_ORDER_STRING= @SO_NUMBER + '%'
	SET @INVOICE_NO_STRING = @INVOICE_NO + '%'
	SET @PARAMDEFINITION = N'@PRM_CUSTOMER_ID VARCHAR(100),@PRM_PRODUCT_ID VARCHAR(100),@PRM_SO_NUMBER VARCHAR(50),@PRM_FROMDATE VARCHAR(50),@PRM_TODATE VARCHAR(50), @PRM_CATEGORY INT, @PRM_INVOICE_NO VARCHAR(50)'

	SET @SQLSTRING = N'
	INSERT INTO #INVOICE_TABLE
	SELECT 
		COOL_LINE_ITEM_NO,COOL_QTY,CSLIT_QTY_SHIPPED,COOL_UNIT_COST,(CSLIT_QTY_SHIPPED*COOL_UNIT_COST), CSLIT_SHIPPED_DATE,
		CSLIT_DESCRIPTION,CSLIT_CUST_PART_NUMBER,LABEL,INV_NO,SUBSTRING(CST_METHOD,0,CHARINDEX (''   #'',CST_METHOD)),
		A.ADDRESS_1,A.ADDRESS_2,A.ADDRESS_3,A.ADDRESS_4,A.CITY,A.STATE,A.ZIPCODE,A.COUNTRY,A.PHONE_NUMBER,A.EMAIL,
		A1.ADDRESS_1,A1.ADDRESS_2,A1.ADDRESS_3,A1.ADDRESS_4,A1.CITY,A1.STATE,A1.ZIPCODE,A1.COUNTRY,A1.PHONE_NUMBER,
		SALESORDER_NO,SO_CUSTOMER_PO_NUMBER,SHIPPING_TERMS/*,DELIVERY_TERMS*/,PART_NUMBER,C_NAME,
		( select DISTINCT (v_name+''/''+A2.CITY ) from Address a2, location l, vendor v where a2.address_id = CST_LOCATION_ADDRESS_ID and a2.address_id = l.address_id and l.vendor_id = v.vendor_id ),
		NULL,NULL,NULL,0,CST_CONFIRMED_ON, CASE SO_IS_SAMPLE WHEN 0 THEN ''SAMPLE'' ELSE (CASE so_is_consignment WHEN 0 THEN ''CONSIGMENT'' ELSE ''REGULAR'' END ) END, ISNULL(COOL_REVISED_DATE,COOL_SCHEDULE_DATE)
	FROM
		CUSTOMER_OPEN_ORDER_LINEITEM COOL INNER JOIN CUSTOMER_SHIPPING_LINE_ITEM_TABLE CSLIT ON COOL.LINEITEM_ID = CSLIT.LINEITEM_ID
		INNER JOIN CUSTOMER_SHIPPING_TABLE CST ON CSLIT.CUSTOMERSHIPPING_ID = CST.CUSTOMERSHIPPING_ID
		LEFT OUTER JOIN ADDRESS A ON CST.CST_BILLING_ADDRESS_ID = A.ADDRESS_ID 
		LEFT OUTER JOIN ADDRESS A1 ON CST.CST_TO_ADDRESS_ID = A1.ADDRESS_ID 
		INNER JOIN SALES_ORDER SO ON COOL.SALESORDER_ID = SO.SALESORDER_ID 
		INNER JOIN PRODUCT P ON COOL.PRODUCT_ID = P.PRODUCT_ID
		INNER JOIN CUSTOMER C ON SO.CUSTOMER_ID = C.CUSTOMER_ID
	WHERE 
		 CSLIT_STATUS = ''SHIPPED'''-- and so_is_consignment = 1 '
		

	IF ( @CUSTOMER_ID IS NOT NULL AND @CUSTOMER_ID <> '0' )
	BEGIN
		SET @SQLSTRING = @SQLSTRING + ' AND SO.CUSTOMER_ID = @PRM_CUSTOMER_ID '
	END	
	IF ( @PRODUCT_ID IS NOT NULL AND @PRODUCT_ID <> '0' and @PRODUCT_ID <> '-1' )
	BEGIN
		SET @SQLSTRING = @SQLSTRING + ' AND COOL.PRODUCT_ID in ( SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING ( @PRM_PRODUCT_ID ))'
	END
	IF ( @SO_NUMBER IS NOT NULL AND @SO_NUMBER <> '0' )
	BEGIN
		SET @SQLSTRING = @SQLSTRING + ' AND SO.SALESORDER_NO LIKE @PRM_SO_NUMBER'    
	END
	IF ( @FROMDATE IS NOT NULL AND @FROMDATE <> '0' )
	BEGIN
		SET @SQLSTRING = @SQLSTRING + ' AND CST_CONFIRMED_ON >= CONVERT( VARCHAR, @PRM_FROMDATE, 101) +'' 12:00AM'' '
	END
	IF ( @TODATE IS NOT NULL AND @TODATE <> '0' )
	BEGIN
		SET @SQLSTRING = @SQLSTRING + ' AND CST_CONFIRMED_ON <= CONVERT( VARCHAR, @PRM_TODATE, 101) +'' 11:59PM'' '
	END
	IF ( @CATEGORY IS NOT NULL AND @CATEGORY <> '3' )
	BEGIN
		IF @CATEGORY = '1'
		BEGIN
			SET @SQLSTRING = @SQLSTRING + ' AND SO.SO_IS_SAMPLE = 1 AND SO.SO_IS_CONSIGNMENT = 1'
		END
		ELSE IF @CATEGORY = '2'
		BEGIN
			SET @SQLSTRING = @SQLSTRING + ' AND SO.SO_IS_SAMPLE = 1 AND SO.SO_IS_CONSIGNMENT = 0'
		END
		ELSE
		BEGIN
			SET @SQLSTRING = @SQLSTRING + ' AND SO.SO_IS_SAMPLE = 0'
		END
		--SET @SQLSTRING = @SQLSTRING + ' AND SO.SO_IS_SAMPLE = @PRM_CATEGORY '
	END

	IF ( @INVOICE_NO IS NOT NULL AND @INVOICE_NO <> '0'  )
	BEGIN
		--SET @SQLSTRING = @SQLSTRING + ' AND CST.INV_NO LIKE @PRM_INVOICE_NO '
		SET @SQLSTRING = @SQLSTRING + ' AND CST.CST_CONFIRMED_ON >= ( SELECT CST_CONFIRMED_ON FROM CUSTOMER_SHIPPING_TABLE WHERE INV_NO = @PRM_INVOICE_NO ) '		
	END
	
	--PRINT @SQLSTRING
	EXECUTE SP_EXECUTESQL @SQLSTRING, @PARAMDEFINITION, @PRM_CUSTOMER_ID = @CUSTOMER_ID,@PRM_PRODUCT_ID = @PRODUCT_ID,@PRM_SO_NUMBER = @SALES_ORDER_STRING,@PRM_FROMDATE = @FROMDATE,@PRM_TODATE = @TODATE, @PRM_CATEGORY=@CATEGORY, @PRM_INVOICE_NO=@INVOICE_NO
	
	SET @CREADITSTRING = N'
	INSERT INTO #INVOICE_TABLE (
		[CREDIT NOTE #],[SHIP DATE],STATUS,[Price each],[Total Amount],REASON,[Qty shipped],
		[Sales Order #],[PO Number],[Customer Name],[Packing Slip #],[INVOICE NUMBER], [Ship Via],
		[Bill To ADDRESS1] ,[Bill To ADDRESS2] ,[Bill To ADDRESS3] ,[Bill To ADDRESS4] ,[Bill To CITY] ,[Bill To STATE] ,
		[Bill To ZIPCODE] ,[Bill To COUNTRY] ,[Bill To PHONE NUMBER],
		[Bill to email] ,[Ship To ADDRESS1] ,[Ship To ADDRESS2],[Ship To ADDRESS3] ,[Ship To ADDRESS4],[Ship To CITY] ,[Ship To STATE] ,
		[Ship To ZIPCODE] ,[Ship To COUNTRY] ,[Ship To PHONE NUMBER] ,
		[LINE ITEM NUMBER],[Item Number],Term,LOCATION,[IS CREDIT/DEBIT],[CONFIRMED DATE], [ORDER TYPE], [LATEST SCH DATE] )
	SELECT 
		INV_NO,CNT.ISSUE_DATE,CNT.STATUS,DISCOUNT_PER_UNIT,DISCOUNT_AMOUNT,REASON,ADJUSTMENT_QTY,SALESORDER_NO,
	        SO_CUSTOMER_PO_NUMBER,C_NAME,LABEL,CREDITNOTE_NO,SUBSTRING(CST_METHOD,0,CHARINDEX (''   #'',CST_METHOD)),
			A.ADDRESS_1,A.ADDRESS_2,A.ADDRESS_3,A.ADDRESS_4,A.CITY,A.STATE,A.ZIPCODE,A.COUNTRY,A.PHONE_NUMBER,A.EMAIL,
			A1.ADDRESS_1,A1.ADDRESS_2,A1.ADDRESS_3,A1.ADDRESS_4,A1.CITY,A1.STATE,A1.ZIPCODE,A1.COUNTRY,A1.PHONE_NUMBER,
			COOL_LINE_ITEM_NO,PART_NUMBER,SHIPPING_TERMS,
		( select DISTINCT (v_name+''/''+A2.CITY ) from Address a2, location l, vendor v where a2.address_id = CST_LOCATION_ADDRESS_ID and a2.address_id = l.address_id and l.vendor_id = v.vendor_id ),
		1,CNT.TIMESTAMP, CASE IS_CREDIT_NOTE WHEN 0 THEN ''CREDIT'' ELSE ''DEBIT'' END, ISNULL(COOL.COOL_REVISED_DATE,COOL.COOL_SCHEDULE_DATE)
	FROM
		CREDIT_NOTE_TABLE CNT INNER JOIN CREDIT_NOTE_LINE_ITEM_TABLE CNLIT ON CNT.CREDITNOTE_ID = CNLIT.CN_ID 
		INNER JOIN SALES_ORDER SO ON CNT.SALESORDER_ID = SO.SALESORDER_ID
		INNER JOIN CUSTOMER C ON SO.CUSTOMER_ID = C.CUSTOMER_ID
		INNER JOIN CUSTOMER_SHIPPING_TABLE CST ON CNT.CUSTOMERSHIPPING_ID = CST.CUSTOMERSHIPPING_ID
		LEFT OUTER JOIN ADDRESS A ON CST.CST_BILLING_ADDRESS_ID = A.ADDRESS_ID 
		LEFT OUTER JOIN ADDRESS A1 ON CST.CST_TO_ADDRESS_ID = A1.ADDRESS_ID 
		LEFT OUTER JOIN CUSTOMER_OPEN_ORDER_LINEITEM COOL ON CNLIT.LINEITEM_ID = COOL.LINEITEM_ID 
		INNER JOIN PRODUCT P ON COOL.PRODUCT_ID = P.PRODUCT_ID  
	WHERE 	
		CNT.Status = ''Approved'' '
	
	IF ( @CUSTOMER_ID IS NOT NULL AND @CUSTOMER_ID <> '0' )
	BEGIN
		SET @CREADITSTRING = @CREADITSTRING + ' AND SO.CUSTOMER_ID = '''+@CUSTOMER_ID+''''
	END	
	IF ( @PRODUCT_ID IS NOT NULL AND @PRODUCT_ID <> '0' and @PRODUCT_ID <> '-1' )
	BEGIN
		SET @CREADITSTRING = @CREADITSTRING + ' AND COOL.PRODUCT_ID in ( SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING ('''+ @PRODUCT_ID +'''))'
	END
	IF ( @SO_NUMBER IS NOT NULL AND @SO_NUMBER <> '0' )
	BEGIN
		SET @CREADITSTRING = @CREADITSTRING + ' AND SO.SALESORDER_NO = '''+@SO_NUMBER+''''
	END
	IF ( @FROMDATE IS NOT NULL AND @FROMDATE <> '0' )
	BEGIN
		SET @CREADITSTRING = @CREADITSTRING + ' AND CNT.ISSUE_DATE >= '''+ CONVERT(VARCHAR,@FROMDATE, 101)+ ' 12:00AM ' +''''
	END
	IF ( @TODATE IS NOT NULL AND @TODATE <> '0' )
	BEGIN
		SET @CREADITSTRING = @CREADITSTRING + ' AND CNT.ISSUE_DATE <= '''+ CONVERT(VARCHAR,@TODATE, 101) +' 11:59PM ' +''''
	END
	IF ( @CATEGORY IS NOT NULL AND @CATEGORY <> '3' )
	BEGIN
		IF @CATEGORY = '1'
		BEGIN
			SET @CREADITSTRING = @CREADITSTRING + ' AND SO.SO_IS_SAMPLE = 1 AND SO.SO_IS_CONSIGNMENT = 1'
		END
		ELSE IF @CATEGORY = '2'
		BEGIN
			SET @CREADITSTRING = @CREADITSTRING + ' AND SO.SO_IS_SAMPLE = 1 AND SO.SO_IS_CONSIGNMENT = 0'
		END
		ELSE
		BEGIN
			SET @CREADITSTRING = @CREADITSTRING + ' AND SO.SO_IS_SAMPLE = 0'
		END
		--SET @CREADITSTRING = @CREADITSTRING + ' AND SO.SO_IS_SAMPLE =' + @CATEGORY 
	END

	IF ( @INVOICE_NO IS NOT NULL AND @INVOICE_NO <> '0' )
	BEGIN
		--SET @CREADITSTRING = @CREADITSTRING + ' AND CST.INV_NO =' +@INVOICE_NO 
		SET @CREADITSTRING = @CREADITSTRING + ' AND CST.CST_CONFIRMED_ON >= ( SELECT CST_CONFIRMED_ON FROM CUSTOMER_SHIPPING_TABLE WHERE INV_NO = ''' + @INVOICE_NO + ''') '
	END
		
	--PRINT '@CREADITSTRING = '+@CREADITSTRING

	UPDATE #INVOICE_TABLE SET [Item Description] = REPLACE( [Item Description], @NewLine,'' ) WHere  [Item Description] like '%' +@NewLine +'%'
	
	EXEC (@CREADITSTRING)

	IF ( @REPORT_TYPE = 'XLREPORT' )
	BEGIN
		SELECT 	[INVOICE NUMBER],[ORDER TYPE],[LATEST SCH DATE],[CONFIRMED DATE],[Customer Name],[CREDIT NOTE #] AS [REFERENCE #],
			[Bill To ADDRESS1],[Bill To ADDRESS2],[Bill To ADDRESS3],[Bill To ADDRESS4],[Bill To CITY],[Bill To STATE],[Bill To ZIPCODE],[Bill To COUNTRY],
			[Ship To ADDRESS1],[Ship To ADDRESS2],[Ship To ADDRESS3],[Ship To ADDRESS4],[Ship To CITY],[Ship To STATE],[Ship To ZIPCODE],[Ship To COUNTRY],
			[Sales Order #],[PO Number],Term,[Ship Date],[Ship Via],[Packing Slip #],[Location],[LINE ITEM NUMBER],
			[Qty shipped],[Item Number],[Item Description],[Customer Part Number],
			[Price each],[Total Amount],[Bill to email],[Bill To PHONE NUMBER],[Ship To PHONE NUMBER],STATUS,REASON, [Qty Pulled]
		FROM #INVOICE_TABLE 
		order by [IS CREDIT/DEBIT], [CONFIRMED DATE]
	END
	ELSE
	BEGIN
		SELECT  [Customer Name],[ORDER TYPE],[Bill To ADDRESS1],[Bill To ADDRESS2],[Bill To ADDRESS3],[Bill To ADDRESS4],[Bill To CITY],[Bill To STATE],
			[Bill To ZIPCODE],[Bill To COUNTRY],[Bill To PHONE NUMBER],[Ship To ADDRESS1],
			[Ship To ADDRESS2],[Ship To ADDRESS3],[Ship To ADDRESS4],[Ship To CITY],[Ship To STATE],[Ship To ZIPCODE],[Ship To COUNTRY],
			[Ship To PHONE NUMBER],[Sales Order #],[LINE ITEM NUMBER],[PO Number],Term,[Ship Date],[Ship Via],[Packing Slip #],[INVOICE NUMBER],
			[Location],[Qty Pulled],[Qty shipped],[Item Number],[Item Description],[Customer Part Number],
			[Price each],[Total Amount],[Bill to email],[CREDIT NOTE #],STATUS,REASON,[IS CREDIT/DEBIT],[CONFIRMED DATE],[LATEST SCH DATE]  
		FROM #INVOICE_TABLE
		order by [IS CREDIT/DEBIT], [CONFIRMED DATE]
	END
END
